﻿/*
* ---------------------------------------------------
* 作者: MTQ 
* 日期： 2021/5/6 12:46:23 
* ---------------------------------------------------
*/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MTQ.BLL.UploadExcel;
using System.Web;
using NPOI.SS.UserModel;
using MTQ.Model;
using MTQ.BLL;
using MTQ.IBLL;
using MTQ.Common;
using System.Data;
namespace MTQ.BLL
{
    public partial class Group_HostInfoService : BaseService<Group_HostInfo>
    {
        public IQueryable<Group_HostInfo> LoadSearchData(Group_HostInfoQuery query)
        {
            var temp = _DbSession.Group_HostInfoRepository.LoadEntities(u => true);
            //可根据所需查询条件自行添加  

            if (!string.IsNullOrEmpty(query.H_Name_CN))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Name_CN.Contains(query.H_Name_CN));
            }

            if (!string.IsNullOrEmpty(query.H_Name_EN))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Name_EN.Contains(query.H_Name_EN));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_PC))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_PC.Contains(query.H_IISURL_PC));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_API))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_API.Contains(query.H_IISURL_API));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_M))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_M.Contains(query.H_IISURL_M));
            }

            if (!string.IsNullOrEmpty(query.H_ServerIP))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_ServerIP.Contains(query.H_ServerIP));
            }

            if (!string.IsNullOrEmpty(query.H_ServerPort))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_ServerPort.Contains(query.H_ServerPort));
            }

            if (!string.IsNullOrEmpty(query.H_DatabaseUser))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabaseUser.Contains(query.H_DatabaseUser));
            }

            if (!string.IsNullOrEmpty(query.H_DatabasePassword))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabasePassword.Contains(query.H_DatabasePassword));
            }

            if (!string.IsNullOrEmpty(query.H_DatabaseName))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabaseName.Contains(query.H_DatabaseName));
            }

            if (!string.IsNullOrEmpty(query.H_Remark))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Remark.Contains(query.H_Remark));
            }

            query.Total = temp.Count();
            return temp.OrderBy(u => u.H_ID).Skip(query.PageSize * (query.PageIndex - 1)).Take(query.PageSize);
        }
        protected override IQueryable<Group_HostInfo> GetBootstrapQueryable<K>(K paramterquery)
        {
            Group_HostInfoQuery query = paramterquery as Group_HostInfoQuery;
            IQueryable<Group_HostInfo> temp = _DbSession.Group_HostInfoRepository.LoadEntities(u => true);


            if (!string.IsNullOrEmpty(query.H_Name_CN))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Name_CN.Contains(query.H_Name_CN));
            }

            if (!string.IsNullOrEmpty(query.H_Name_EN))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Name_EN.Contains(query.H_Name_EN));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_PC))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_PC.Contains(query.H_IISURL_PC));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_API))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_API.Contains(query.H_IISURL_API));
            }

            if (!string.IsNullOrEmpty(query.H_IISURL_M))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_IISURL_M.Contains(query.H_IISURL_M));
            }

            if (!string.IsNullOrEmpty(query.H_ServerIP))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_ServerIP.Contains(query.H_ServerIP));
            }

            if (!string.IsNullOrEmpty(query.H_ServerPort))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_ServerPort.Contains(query.H_ServerPort));
            }

            if (!string.IsNullOrEmpty(query.H_DatabaseUser))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabaseUser.Contains(query.H_DatabaseUser));
            }

            if (!string.IsNullOrEmpty(query.H_DatabasePassword))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabasePassword.Contains(query.H_DatabasePassword));
            }

            if (!string.IsNullOrEmpty(query.H_DatabaseName))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_DatabaseName.Contains(query.H_DatabaseName));
            }

            if (!string.IsNullOrEmpty(query.H_Remark))
            {
                temp = temp.Where<Group_HostInfo>(u => u.H_Remark.Contains(query.H_Remark));
            }

            return temp;
        }

        public IQueryable<Group_HostInfo> GetIQueryable(Group_HostInfoQuery query)
        {
            return this.GetBootstrapQueryable<Group_HostInfoQuery>(query);
        }
        public static bool ReadExcel(string filename, Guid userid, ref string msg)
        {
            DataTable dt = new DataTable();
            string xlsPath = filename;
            dt = GetDataNoPage("Group_HostInfo", "  1<>1   ", "H_ID").Tables[0];
            string errorMessage = string.Empty;
            var up = new UploadService().UploadXls(Group_HostInfoService.ExcuteExcelSql, xlsPath, dt, out errorMessage);
            if (!up)
            { return false; }


            DateTime datetime = System.DateTime.Now;
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {


            }



            return BLL.Group_HostInfoService.DateTableSave(dt, "Group_HostInfo");
        }


        public static void ExcuteExcelSql(IWorkbook wookBook, DataTable dtvc, out string errormessage)
        {
            string ErrorMessage = string.Empty;
            List<ExcelTableColumn> listExcelTableColumn = new List<ExcelTableColumn>();
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_ID", ExcelColumnName = "H_ID", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_Name_CN", ExcelColumnName = "H_Name_CN", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_Name_EN", ExcelColumnName = "H_Name_EN", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_IISURL_PC", ExcelColumnName = "H_IISURL_PC", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_IISURL_API", ExcelColumnName = "H_IISURL_API", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_IISURL_M", ExcelColumnName = "H_IISURL_M", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_ServerIP", ExcelColumnName = "H_ServerIP", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_ServerPort", ExcelColumnName = "H_ServerPort", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_DatabaseUser", ExcelColumnName = "H_DatabaseUser", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_DatabasePassword", ExcelColumnName = "H_DatabasePassword", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_DatabaseName", ExcelColumnName = "H_DatabaseName", ExcelColumnIndex = -1 });
            listExcelTableColumn.Add(new ExcelTableColumn() { TableColumnName = "H_Remark", ExcelColumnName = "H_Remark", ExcelColumnIndex = -1 });
            ISheet sheet = wookBook.GetSheet("sheet");
            int columnindex = 0;
            int firstRowNum = sheet.FirstRowNum + 1;
            if (sheet.GetRow(0).GetCell(0).StringCellValue.StartsWith("上传项目数据"))
            {
                columnindex = 1;
                firstRowNum++;
            }
            for (int i = 0; i < 100; i++)
            {
                IRow row = sheet.GetRow(columnindex);
                if (row.GetCell(0) == null || row.GetCell(1) == null)
                { continue; }
                try
                {
                    string columnName = row.GetCell(i).StringCellValue;
                    if (string.IsNullOrEmpty(columnName))
                    { break; }
                    ExcelTableColumn etc = listExcelTableColumn.Where(x => x.ExcelColumnName == columnName).FirstOrDefault();
                    if (etc == null)
                    { continue; }
                    etc.ExcelColumnIndex = i;
                    if (listExcelTableColumn.Where(x => x.ExcelColumnIndex == -1).Count() == 0)
                    { break; }
                }
                catch
                { break; }
            }
            if (listExcelTableColumn.Where(x => x.ExcelColumnIndex == -1).Count() > 0)
            { errormessage = "列名不正确"; }
            else
            {
                int rowCount = sheet.LastRowNum;
                for (int j = firstRowNum; j <= sheet.LastRowNum; j++)
                {
                    DataRow dr = dtvc.NewRow();
                    IRow row = sheet.GetRow(j);
                    if (row == null) { continue; }
                    foreach (var item in listExcelTableColumn)
                    {
                        NPOI.XSSF.UserModel.XSSFCell cell = row.GetCell(item.ExcelColumnIndex) as NPOI.XSSF.UserModel.XSSFCell;
                        if (cell == null)
                        {
                            dr[item.TableColumnName] = "";
                        }
                        else
                        {
                            if (cell.CellType == CellType.Numeric)
                            {
                                if (item.TableColumnType == TableColumnType.DateTime)
                                { dr[item.TableColumnName] = cell.DateCellValue; }
                                else
                                { dr[item.TableColumnName] = cell.GetRawValue(); }
                            }
                            else if (cell.CellType == CellType.Blank)
                            { }
                            else
                            { dr[item.TableColumnName] = cell.StringCellValue; }
                        }
                    }
                    dtvc.Rows.Add(dr);
                }
                errormessage = "";
            }
        }
    }
}

